﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Maticsoft.DBUtility;
using System.Collections;
using Aspose.Cells;
using System.Drawing;
using System.IO;
using System.Web;

namespace EnvironmentManagerSystem.BLL
{
    public class TreeViewPanel
    {
        /// <summary>
        /// 获取Tree信息
        /// </summary>
        /// <returns></returns>
        public static DataTable GetTree()
        {
            try
            {
                return DbHelperMySQL.Query(" select id,pid,text,value,HavaChildNode from tbs001_tree ").Tables[0];
            }
            catch { return null; }
        }

        /// <summary>
        /// 导出Tree信息
        /// </summary>
        /// <returns></returns>
        public static void Export(List<Hashtable> list, string where)
        {
            try
            {
                ArrayList ary = new ArrayList();
                string com = "";
                foreach (Hashtable hs in list)
                {
                    ary.AddRange(hs.Keys);
                    foreach(DictionaryEntry di in hs)
                    {
                        com += di.Value + ",";
                    }
                }
                string sql = string.Format(
                " select {0} from tbs001_developprojectbasicinfo,tbs001_huanjing,tbx001_shengtai,tbs001_constructionunit,tbs001_evaluationunit where " +
                " tbs001_developprojectbasicinfo.ProjectName=tbs001_huanjing.name and tbs001_developprojectbasicinfo.ProjectName=tbx001_shengtai.name and " +
                " tbs001_developprojectbasicinfo.js=tbs001_constructionunit.id and tbs001_developprojectbasicinfo.hp=tbs001_evaluationunit.id {1}", com.TrimEnd(','), where);
                DataTable dt = DbHelperMySQL.Query(sql).Tables[0];
                export ex = new export();
                ex.Ary = ary;
                ex.Dt = dt;
                ex.Name = "数据";
                List<export> lista = new List<export> { ex };
                port.export(lista, "数据");
            }
            catch { }
        }
    }
    public class port
    {
        //导出操作
        public static void export(List<export> list, String path)
        {
            if (list.Count > 0)
            {
                Workbook wb = new Workbook();
                int intSeet = 0;

                #region "表格样式"
                wb.Styles.Add();
                wb.Styles[0].VerticalAlignment = TextAlignmentType.Center;
                wb.Styles[0].HorizontalAlignment = TextAlignmentType.Center;
                wb.Styles[0].Font.Color = ColorTranslator.FromHtml("#000000");
                wb.Styles[0].Font.Size = 10;
                wb.Styles[0].Font.Name = "宋体";
                wb.Styles[0].Font.IsBold = true;
                wb.Styles[0].Pattern = BackgroundType.Solid;
                wb.Styles[0].ForegroundColor = ColorTranslator.FromHtml("#CCFFFF");
                wb.Styles[0].Borders[BorderType.TopBorder].Color = Color.Black;
                wb.Styles[0].Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
                wb.Styles[0].Borders[BorderType.BottomBorder].Color = Color.Black;
                wb.Styles[0].Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
                wb.Styles[0].Borders[BorderType.LeftBorder].Color = Color.Black;
                wb.Styles[0].Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
                wb.Styles[0].Borders[BorderType.RightBorder].Color = Color.Black;
                wb.Styles[0].Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
                wb.Styles.Add();
                wb.Styles[1].VerticalAlignment = TextAlignmentType.Center;
                wb.Styles[1].HorizontalAlignment = TextAlignmentType.Center;
                wb.Styles[1].Font.Color = ColorTranslator.FromHtml("#000000");
                wb.Styles[1].Font.Size = 10;
                wb.Styles[1].Font.Name = "宋体";
                wb.Styles[1].ForegroundColor = Color.Yellow;
                wb.Styles[1].Pattern = BackgroundType.Solid;
                wb.Styles[1].Font.IsBold = false;
                wb.Styles[1].Borders[BorderType.TopBorder].Color = Color.Black;
                wb.Styles[1].Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
                wb.Styles[1].Borders[BorderType.BottomBorder].Color = Color.Black;
                wb.Styles[1].Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
                wb.Styles[1].Borders[BorderType.LeftBorder].Color = Color.Black;
                wb.Styles[1].Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
                wb.Styles[1].Borders[BorderType.RightBorder].Color = Color.Black;
                wb.Styles[1].Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
                wb.Styles.Add();
                wb.Styles[2].VerticalAlignment = TextAlignmentType.Center;
                wb.Styles[2].HorizontalAlignment = TextAlignmentType.Left;
                wb.Styles[2].Font.Size = 10;
                wb.Styles[2].Font.Name = "宋体";
                wb.Styles[2].Font.IsBold = false;
                wb.Styles[2].Borders[BorderType.TopBorder].Color = Color.Black;
                wb.Styles[2].Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
                wb.Styles[2].Borders[BorderType.BottomBorder].Color = Color.Black;
                wb.Styles[2].Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
                wb.Styles[2].Borders[BorderType.LeftBorder].Color = Color.Black;
                wb.Styles[2].Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
                wb.Styles[2].Borders[BorderType.RightBorder].Color = Color.Black;
                wb.Styles[2].Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
                #endregion

                #region "导出报表"

                foreach (export ex in list)
                {
                    Worksheet wr = wb.Worksheets[intSeet];
                    wr.Name = ex.Name;
                    if (ex.Ary.Count <= 0)
                    {
                        wr.Cells.Merge(0, 0, 5, 5);
                        wr.Cells[0, 0].PutValue("请选择数据");
                    }
                    else
                    {
                        for (int i = 0; i < ex.Ary.Count; i++)
                        {
                            wr.Cells[0, i].PutValue(ex.Ary[i].ToString());
                            wr.Cells[0, i].Style = wb.Styles[0];
                            wr.Cells.SetColumnWidth(i, 30);
                        }
                        for (int i = 0; i < ex.Dt.Rows.Count; i++)
                        {
                            for (int j = 0; j < ex.Ary.Count; j++)
                            {
                                wr.Cells[i + 1, j].PutValue(ex.Dt.Rows[i][j].ToString().Equals("") ? "" : ex.Dt.Rows[i][j].ToString());
                            }
                            wr.Cells.CreateRange(i + 1, 0, 1, ex.Ary.Count).Style = wb.Styles[2];
                        }
                        if (intSeet < list.Count - 1)
                            intSeet = wb.Worksheets.Add(SheetType.Worksheet);
                    }
                }

                wb.Save(HttpUtility.UrlEncode(path) + ".xls", FileFormatType.Default, SaveType.OpenInExcel, HttpContext.Current.Response);

                #endregion
            }
        }
    }
    public class export
    {
        public String Name { get; set; }
        public ArrayList Ary { get; set; }
        public DataTable Dt { get; set; }
    }
}
